There are two tables in my database. First table is room and second table is reservation. In my room table

id room_no type rate
1  13       1b  1000
2  14       2b  2000
3  15       3b  3000
4  16       1b  1000
5  17       2b  2000
6  18       3b  3000

In my reservation table

id room_no check_in     check_out
1   13     23-2-2016     24-2-2016
2   14     24-2-2016     25-2-2016
1   13     25-2-2016     26-2-2016
1   13     27-2-2016     29-2-2016
1   13     1-3-2016      2-3-2016
1   13     7-3-2016      7-3-2016

"SELECT room_no,type,rate 
             FROM room 
             WHERE room_no not in 
             (select IFNULL(GROUP_CONCAT(room_no),0) 
             FROM reservation 
             WHERE check_out >= '$check_in' AND check_in <= '$check_out')"

when I select a date 24-2-2016 to 27-2-2016 then it display

room_no check_in     check_out

  14     24-2-2016     25-2-2016
  15     25-2-2016     26-2-2016
  16     27-2-2016     29-2-2016
  17     1-3-2016      2-3-2016
  18     7-3-2016      7-3-2016

but I want all available rooms.


solutions

To get occupied rooms for the period specified, i.e '2016-02-27'-'2016-02-24', you can use:

SELECT DISTINCT room_no
FROM reservation
WHERE check_in <= '2016-02-27' AND check_out >= '2016-02-24'

Output:

room_no
=======
13
14

To get available rooms you can use the previous query like this:

SELECT *
FROM room
WHERE room_no NOT IN (
   SELECT DISTINCT room_no
   FROM reservation
   WHERE check_in <= '2016-02-27' AND check_out >= '2016-02-24')

Output:

id, room_no, type, rate
=======================
3,   15,     3b,   3000
4,   16,     1b,   1000
5,   17,     2b,   2000
6,   18,     3b,   3000
ob_start();
ob_start();
ob_start();
ob_start();

<script></script>